import csv
import uuid

import happybase as happybase
import pymysql

from csvToDatabase.convertDateToNumber import convert_date_to_number
from csvToDatabase.convertTimeToSeconds import convert_time_to_seconds

# 创建mysql数据库连接
conn = pymysql.connect(host="localhost", port=3306, user="root", passwd="root", db="data_analyze")
cursor = conn.cursor()
# mysql插入数据语句
insert_query = "INSERT INTO hot_search (hot_content, search_link, ranking_time, highest_rank, duration, duration_number, search_index_peak, platform, date_number) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s)"


# 创建hbase数据库链接
connection = happybase.Connection('192.168.139.136')
# 选择要操作的表
table_name = 'hot_search'
table = connection.table(table_name)
# create 'hot_search', 'cf'  建表语句
# alter 'hot_search', 'cf:hot_content', 'cf:search_link', 'cf:ranking_time', 'cf:highest_rank', 'cf:duration', 'cf:duration_number', 'cf:search_index_peak', 'cf:platform'

# 文件名列表
file_names = ['1.csv', '2.csv', '3.csv', '4.csv']

# 遍历文件名
for file_name in file_names:
    # 构建文件路径
    file_path = file_name
    file_name_without_extension = int(file_path.replace(".csv", ""))

    # 打开CSV文件
    with open(file_path, 'r') as file:
        # 创建CSV读取器
        reader = csv.reader(file)

        for row in reader:
            hot_content = row[0]
            search_link = row[1]
            ranking_time = row[2]
            highest_rank = int(row[3])
            duration = row[4]
            duration_number = convert_time_to_seconds(duration)
            date_number = convert_date_to_number(ranking_time)
            search_index_peak = row[5]
            numeric_value = round(float(search_index_peak[:-1]), 1)
            platform = file_name_without_extension

            # 存入mysql
            values = (hot_content, search_link, ranking_time, highest_rank, duration, duration_number, numeric_value, platform, date_number)
            cursor.execute(insert_query, values)

            # 存入hbase
            data = {
                'cf:hot_content': hot_content.encode(),
                'cf:search_link': search_link.encode(),
                'cf:ranking_time': ranking_time.encode(),
                'cf:highest_rank': str(highest_rank).encode(),
                'cf:duration': duration.encode(),
                'cf:duration_number': str(duration_number).encode(),
                'cf:search_index_peak': str(numeric_value).encode(),
                'cf:platform': str(platform).encode(),
                'cf:date_number': str(date_number).encode(),
            }
            print(data)
            unique_id = uuid.uuid4()  # 使用UUID来生成唯一的标识符
            row_key = str(unique_id)  # 将UUID转换为字符串作为行键
            table.put(row_key.encode(), data)

# 提交更改并关闭mysql连接
conn.commit()
conn.close()

# 关闭hbase连接
connection.close()
